/**
 * 
 */
package com.feib.soeasy.dao.jdbc;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.TransferFeedbackDao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.util.UserUtil;

/**
 * @author Jimmy Liu
 *
 */
@Repository("transferFeedbackDao")
public class TransferFeedbackJdcbDao extends JdbcDaoSupport implements TransferFeedbackDao{

	protected Logger logger = LoggerFactory.getLogger(this.getClass());

	@Resource(name = "tempDirectory")
	private String tempDirectory;

	@Resource(name = "dataSource")
	public void setMyDataSource(DataSource dataSource) {
		super.setDataSource(dataSource); 
	}


	/**
	 * 優利整批轉帳媒體檔
	 * @param transferTime 傳輸日期
	 * @param transferType 檔案格式: 
	 *         0: 超商扣款檔名：P1003_eBilling_yyyymmdd.TXT
	 *         1: 超商撥款檔名：P2004_eBilling_yyyymmdd.TXT
	 *         2: 郵局撥款檔名: A1002_eBilling_yyyymmdd.TXT
	 * @return
	 */
	public String makeTransferMediaFile(Date transferTime, int transferType)
	{
		List<Object> args = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		String localFolder = tempDirectory; //暫存目錄
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		String fileName = "P2004_eBilling_"+ dateFormat.format(transferTime)+".TXT"; //超商撥款檔名

		if (1 == transferType)
		{
			// 超商撥款, 檔案內會只有一筆資
			sql.append("SELECT 'XXXXXXXXXXXX' as GROUP_NO, SUM(T.TXAMT) AS TXAMT,  SUM(T.CHANNEL_COUNT) AS CHANNEL_COUNT, 'ALLSTORE' as CHANNEL_CODE,'XXXXXXXXXXXX' as ACCOUNT_NO ");
			sql.append("FROM TRANSFER_FEEDBACK T ");
			sql.append("WHERE T.TRANSFER_CODE = 1 AND T.TRANSFER_BY is not null "); // 只要檢查  TRANSFER_FEEDBACK 已經撥款的資料
			sql.append("and convert(varchar(8), T.TRANSFER_TIME, 112) = ? "); // 撥款日期
			args.add(dateFormat.format(transferTime));
			sql.append("and T.CHANNEL_CODE IN ('D','E','F','G')"); //限超商
			logger.debug("P2004 SQL="+sql+"; args="+args);

			String P2004= this.getJdbcTemplate().query(sql.toString(), args.toArray(), new StoreMediaFileResultSetExtractor(localFolder, fileName, transferTime));

			List<Object> args2 = new ArrayList<Object>();
			StringBuffer sql2 = new StringBuffer();
			
			
			// 超商扣款, 多筆
			sql2.append("SELECT T.GROUP_NO, SUM(T.TXAMT) AS TXAMT,  SUM(T.CHANNEL_COUNT) AS CHANNEL_COUNT, T.CHANNEL_CODE, a.GROUP_NAME,T.ACCOUNT_NO ");
			sql2.append("FROM TRANSFER_FEEDBACK T ");
			sql2.append(" inner join app_group  a ").append("\r\n");				
			sql2.append(" on t.group_no=a.group_no").append("\r\n");	
			sql2.append("WHERE T.TRANSFER_CODE = 1 AND T.TRANSFER_BY is not null "); // 只要檢查  TRANSFER_FEEDBACK 已經撥款的資料
			sql2.append("and convert(varchar(8), T.TRANSFER_TIME, 112) = ? "); // 撥款日期
			sql2.append("and T.CHANNEL_CODE IN ('D','E','F','G')"); //限超商
			sql2.append("GROUP BY T.GROUP_NO, T.CHANNEL_CODE,a.GROUP_NAME ,T.ACCOUNT_NO ");
			args2.add(dateFormat.format(transferTime));
			logger.debug("P1003 SQL="+sql+"; args="+args);

			fileName = "P1003_eBilling_"+ dateFormat.format(transferTime)+".TXT";


			String P1003= this.getJdbcTemplate().query(sql2.toString(), args2.toArray(), new StoreMediaFileResultSetExtractor(localFolder, fileName, transferTime));

			ArrayList<String> filesToAdd = new ArrayList<String>();
			if(StringUtils.isNotBlank(P2004)){
				filesToAdd.add(P2004);
			}else{
				logger.info("P2004檔案 {}不存在！");

			}
			if(StringUtils.isNotBlank(P1003)){
				filesToAdd.add(P1003);
			}else{
				logger.info("P1003檔案 {}不存在！");
			}
			String zipName="P2004_P1003eBilling_"+ dateFormat.format(transferTime)+".zip";
			return this.zipOutputFile(zipName, filesToAdd);

		}
		else if (2 == transferType)
		{
			// 郵局撥款
			sql.append("SELECT T.GROUP_NO, SUM(T.TXAMT) AS TXAMT,  SUM(T.CHANNEL_COUNT) AS CHANNEL_COUNT, T.CHANNEL_CODE, a.GROUP_NAME,T.ACCOUNT_NO ");
			sql.append("FROM TRANSFER_FEEDBACK T ");
			sql.append(" inner join app_group  a ").append("\r\n");				
			sql.append(" on t.group_no=a.group_no").append("\r\n");	
			sql.append("WHERE T.TRANSFER_CODE = 1 AND T.TRANSFER_BY is not null "); // 只要檢查  TRANSFER_FEEDBACK 已經撥款的資料
			sql.append("and convert(varchar(8), T.TRANSFER_TIME, 112) = ? "); // 撥款日期
			sql.append(" and T.CHANNEL_CODE  = 'H' "); //限超商
			sql.append("GROUP BY T.GROUP_NO, T.CHANNEL_CODE, a.GROUP_NAME,T.ACCOUNT_NO");
			args.add(dateFormat.format(transferTime));		

			fileName = "A1002_eBilling_"+ dateFormat.format(transferTime)+".TXT"; //超商扣款檔名
			return this.getJdbcTemplate().query(sql.toString(), args.toArray(), new PostMediaFileResultSetExtractor(localFolder, fileName, transferTime));
		}else{
			// 超商扣款, 多筆
			sql.append("SELECT GROUP_NO, SUM(T.TXAMT) AS TXAMT,  SUM(T.CHANNEL_COUNT) AS CHANNEL_COUNT, CHANNEL_CODE, GROUP_NAME,ACCOUNT_NO");
			sql.append("FROM TRANSFER_FEEDBACK T ");
			sql.append(" inner join app_group  a ").append("\r\n");				
			sql.append(" on t.group_no=a.group_no").append("\r\n");	
			sql.append("WHERE T.TRANSFER_CODE = 1 AND T.TRANSFER_BY is not null "); // 只要檢查  TRANSFER_FEEDBACK 已經撥款的資料
			sql.append("and convert(varchar(8), T.TRANSFER_TIME, 112) = ? "); // 撥款日期
			sql.append("and T.CHANNEL_CODE IN ('D','E','F','G')"); //限超商
			sql.append("GROUP BY GROUP_NO, CHANNEL_CODE,GROUP_NAME,ACCOUNT_NO ");
			args.add(dateFormat.format(transferTime));
			return this.getJdbcTemplate().query(sql.toString(), args.toArray(), new StoreMediaFileResultSetExtractor(localFolder, fileName, transferTime));
		}
	}

	class StoreMediaFileResultSetExtractor implements ResultSetExtractor<String>
	{
		private String localFolder;
		private Date transferTime;
		private String fileName;

		private String cTransferTime = null; //轉帳日期民國年
		private Map<String, String> accountMap;

		public StoreMediaFileResultSetExtractor(String localFolder, String fileName, Date transferTime)
		{
			this.localFolder = localFolder;
			this.transferTime = transferTime;
			this.fileName = fileName;
			this.accountMap = new HashMap<String, String>();
			this.accountMap.put("XXXXXXXXXXXX", "02500100008977");

			// 轉帳日期轉民國年
			DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
			this.cTransferTime = String.valueOf(Integer.parseInt(dateFormat.format(transferTime)) - 19110000);

			if (this.cTransferTime.length() < 7)
				this.cTransferTime = "0"+ this.cTransferTime;
		}

		@Override
		public String extractData(ResultSet rs) throws SQLException,
		DataAccessException {

			boolean empty = true;

			// 檔案名稱
			String sFileName = null;
			BufferedOutputStream out = null;
			FileOutputStream fos = null;

			try {
				byte[] tempBytes = null;

				boolean firstRecord = true;
				StringBuffer sb = null;

				while (rs.next()) 
				{
					if (empty)
						empty = false;

					if (firstRecord)
					{
						firstRecord = false;
						sFileName = this.localFolder + "/" + this.fileName ;
						fos = new FileOutputStream(new File(sFileName));
						out = new BufferedOutputStream(fos);

						out.write(" H\r\n".getBytes()); //第一行
					}

					String groupNo = rs.getString("GROUP_NO");
					BigDecimal txamt = rs.getBigDecimal("TXAMT");
					BigDecimal channelCount = rs.getBigDecimal("CHANNEL_COUNT");
					String channelCode = rs.getString("CHANNEL_CODE");
					String accountNo = rs.getString("ACCOUNT_NO");

					out.write(" ".getBytes()); // 空白
					if("XXXXXXXXXXXX".equals(groupNo)){
						out.write(getAccountNoByGroupNo(groupNo).getBytes());
					}else{
						out.write(accountNo.getBytes());
					}
					// 存款帳號
					out.write("014".getBytes()); //摘要, EX:014薪轉 (請依據所需性質放置)

					//轉帳金額
					txamt = txamt.setScale(2,BigDecimal.ROUND_HALF_UP); // 四捨五入到小數兩位
					String sTxamt = txamt.toString();
					String[] sarry = sTxamt.split("\\.");
					// 交易金額整數位
					tempBytes = sarry[0].getBytes();
					if (11 != tempBytes.length)
					{
						byte[] tb = new byte[11];
						for(int i=0; i<11; i++)
						{
							tb[i] = '0';
						}
						System.arraycopy(tempBytes, 0, tb, 11 - tempBytes.length , tempBytes.length);
						tempBytes = tb;
					}
					out.write(tempBytes);

					//轉帳日期
					out.write(cTransferTime.getBytes());

					sb = new StringBuffer();
					// 存摺摘要
					if ("ALLSTORE".equalsIgnoreCase(channelCode))
					{
						sb.append("超商");
					}
					else if ("D".equalsIgnoreCase(channelCode))
					{
						sb.append("統一");
					}
					else if ("E".equalsIgnoreCase(channelCode))
					{
						sb.append("全家");
					}
					else if ("F".equalsIgnoreCase(channelCode))
					{
						sb.append("萊爾富");
					}
					else if ("G".equalsIgnoreCase(channelCode))
					{
						sb.append("OK");
					}
					sb.append(channelCount.toString()).append("筆");
					tempBytes = sb.toString().getBytes();
					if (14 != tempBytes.length)
					{
						byte[] tb = new byte[14];
						for(int i=0; i<14; i++)
						{
							tb[i] = ' ';
						}
						System.arraycopy(tempBytes, 0, tb, 14 - tempBytes.length , tempBytes.length);
						tempBytes = tb;
					}
					out.write(tempBytes);

					//換行
					out.write("\r\n".getBytes());


				}// end while

				/* 將緩衝區中的資料全部寫出 */
				out.flush();

				/* 關閉串流 */
				out.close();

				out = null;

				/* 資料庫無資料，刪除空檔 */
				if (empty)
				{
					FileUtils.forceDelete(new File(sFileName));
					sFileName = null;
				}	
			}
			catch (Exception e) {
				logger.error("", e);
				try {
					FileUtils.forceDelete(new File(sFileName));
				} catch (IOException e1) {
				}
				sFileName = null;
				throw new SQLException("輸出檔案發生錯誤: "+ e.getMessage());
			}
			finally
			{
				if (null != out)
				{
					try {
						out.flush();
						out.close();
					} catch (IOException e) {
					}
					out = null;
				}
			}


			return sFileName;
		}

		/**
		 * 以業者代號取得 存款或提款帳號
		 * @param groupNo
		 * @return
		 */
		public String getAccountNoByGroupNo(String groupNo)
		{
			String accountNo = this.accountMap.get(groupNo);
			if (null != accountNo)
				return accountNo;
			this.accountMap.put(groupNo, accountNo); // 放置cache
			return accountNo;

		}

	}

	class PostMediaFileResultSetExtractor implements ResultSetExtractor<String>
	{
		private String localFolder;
		private Date transferTime;
		private String fileName;

		private String cTransferTime = null; //轉帳日期民國年
		private Map<String, String> accountMap;

		public PostMediaFileResultSetExtractor(String localFolder, String fileName, Date transferTime)
		{
			this.localFolder = localFolder;
			this.transferTime = transferTime;
			this.fileName = fileName;
			this.accountMap = new HashMap<String, String>();

			// 轉帳日期轉民國年
			DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
			this.cTransferTime = String.valueOf(Integer.parseInt(dateFormat.format(transferTime)) - 19110000);

			if (this.cTransferTime.length() < 7)
				this.cTransferTime = "0"+ this.cTransferTime;
		}

		@Override
		public String extractData(ResultSet rs) throws SQLException,
		DataAccessException {

			boolean empty = true;

			// 檔案名稱
			String sFileName = null;
			BufferedOutputStream out = null;
			FileOutputStream fos = null;

			try {
				byte[] tempBytes = null;

				boolean firstRecord = true;
				StringBuffer sb = null;

				while (rs.next()) 
				{
					if (empty)
						empty = false;

					if (firstRecord)
					{
						firstRecord = false;
						sFileName = this.localFolder + "/" + this.fileName ;
						fos = new FileOutputStream(new File(sFileName));
						out = new BufferedOutputStream(fos);
					}


					BigDecimal txamt = rs.getBigDecimal("TXAMT");
					BigDecimal channelCount = rs.getBigDecimal("CHANNEL_COUNT");
					
					String accountNo = rs.getString("ACCOUNT_NO");
					String groupName = rs.getString("GROUP_NAME");
					
					out.write(" ".getBytes()); // 空白
					out.write("00".getBytes()); // 幣別00
					out.write("A10".getBytes()); // 交易代號
					
					out.write("87112825730015".getBytes());//帳號
					
					out.write("060".getBytes()); // 連動轉
					
				    out.write(accountNo.getBytes());//對方帳號
					
				    sb = new StringBuffer();
					//傳票摘要1
				    sb.append("郵局");					
					sb.append(channelCount.toString()).append("筆");
					tempBytes = sb.toString().getBytes();
					if (42 != tempBytes.length)
					{
						byte[] tb = new byte[42];
						for(int i=0; i<42; i++)
						{
							tb[i] = ' ';
						}
						System.arraycopy(tempBytes, 0, tb, 42 - tempBytes.length , tempBytes.length);
						tempBytes = tb;
					}
					out.write(tempBytes);
				    
					sb.delete(0, sb.length());
					//傳票摘要2
					sb.append(groupName);
					sb.append(channelCount.toString()).append("筆");
					tempBytes = sb.toString().getBytes();
					if (42 != tempBytes.length)
					{
						byte[] tb = new byte[42];
						for(int i=0; i<42; i++)
						{
							tb[i] = ' ';
						}
						System.arraycopy(tempBytes, 0, tb, 42 - tempBytes.length , tempBytes.length);
						tempBytes = tb;
					}
					out.write(tempBytes);
					
					
					
					//交易金額
					txamt = txamt.setScale(2,BigDecimal.ROUND_HALF_UP); // 四捨五入到小數兩位
					String sTxamt = txamt.toString();
					String[] sarry = sTxamt.split("\\.");
					// 交易金額整數位
					tempBytes = sarry[0].getBytes();
					if (11 != tempBytes.length)
					{
						byte[] tb = new byte[11];
						for(int i=0; i<11; i++)
						{
							tb[i] = '0';
						}
						System.arraycopy(tempBytes, 0, tb, 11 - tempBytes.length , tempBytes.length);
						tempBytes = tb;
					}
					out.write(tempBytes);

					//轉帳日期
					//out.write(cTransferTime.getBytes());

					StringBuffer result = new StringBuffer();
					
					for(int i=0; i<14; i++)
					{
						result.append(" ");//銷帳帳戶
					}
					out.write(result.toString().getBytes());
					
					result.delete(0, result.length());
					
					for(int i=0; i<14; i++)
					{
						result.append(" ");//授信帳號
					}
					out.write(result.toString().getBytes());
				
					for(int i=0; i<16; i++)
					{
						result.append(" ");//分號
					}
					out.write(result.toString().getBytes());
				
					
					for(int i=0; i<11; i++)
					{
						result.append(" ");//銷帳編號
					}
					out.write(result.toString().getBytes());
				
					
					for(int i=0; i<10; i++)
					{
						result.append(" ");//債清戶id
					}
					out.write(result.toString().getBytes());
				
					
					

					//換行
					out.write("\r\n".getBytes());


				}// end while

				/* 將緩衝區中的資料全部寫出 */
				out.flush();

				/* 關閉串流 */
				out.close();

				out = null;

				/* 資料庫無資料，刪除空檔 */
				if (empty)
				{
					FileUtils.forceDelete(new File(sFileName));
					sFileName = null;
				}	
			}
			catch (Exception e) {
				logger.error("", e);
				try {
					FileUtils.forceDelete(new File(sFileName));
				} catch (IOException e1) {
				}
				sFileName = null;
				throw new SQLException("輸出檔案發生錯誤: "+ e.getMessage());
			}
			finally
			{
				if (null != out)
				{
					try {
						out.flush();
						out.close();
					} catch (IOException e) {
					}
					out = null;
				}
			}


			return sFileName;
		}

		/**
		 * 以業者代號取得 存款或提款帳號
		 * @param groupNo
		 * @return
		 */
		public String getAccountNoByGroupNo(String groupNo)
		{
			String accountNo = this.accountMap.get(groupNo);
			if (null != accountNo)
				return accountNo;

			// TODO 從資料庫取得 存款或提款帳號			
			// accountNo = XXXX;

			this.accountMap.put(groupNo, accountNo); // 放置cache
			return accountNo;

		}

	}

	public void doTransferFeedback(Group group, String[] channels, Date[] qryDateFrom, Date[] qryDateTo,List<String> queryGroupType) {
		StringBuffer sql = new StringBuffer();
		List<Object> args = new ArrayList<Object>();   
		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = qryDateFrom[i];
			Date dateTo = qryDateTo[i];

			if(!"null".equalsIgnoreCase(channel) && StringUtils.isNotBlank(channel)){


				sql.append("update TRANSFER_FEEDBACK t   ");

				sql.append("SET TRANSFER_CODE =1,TRANSFER_TIME=CONVERT(varchar(12), GETDATE(), 102)  ");

				sql.append(",TRANSFER_BY  =' ?', UPDATE_BY='?'  ");

				sql.append(",UPDATE_DATE=CONVERT(varchar(12), GETDATE(), 102)   ");


				args.add(UserUtil.getUserGroup().getGroupNo() + "_" + UserUtil.getUserId());
				args.add(UserUtil.getUserGroup().getGroupNo() + "_" + UserUtil.getUserId());

				sql.append(" WHERE transfer_code = 0 ").append("\r\n");


				sql.append(" AND CHANNEL_CODE = ? ").append("\r\n");	
				args.add(channel);


				/* 沒有傳入group就抓全部 */
				if(null != group.getGroupNo()){
					sql.append(" and group_no = ? ").append("\r\n");	
					args.add(group.getGroupNo());
				}


				StringBuffer secs = new StringBuffer();
				for (String x: queryGroupType)
				{
					secs.append(" ?").append(",");
					args.add(x);
				}
				sql.append(" AND Group_Type IN (").append(secs.substring(0, secs.length()-1)).append(")").append("\r\n");



				sql.append(" and convert(varchar(8),fbTime,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112)  ").append("\r\n");	
				args.add(dateFrom);
				args.add(dateTo);

				logger.debug("updateFeedbackLogResultCode SQL="+sql +"; args=" + args);

				this.getJdbcTemplate().update(sql.toString(), args.toArray());	   

			}
		}



	}

	/**
	 * 压缩文件
	 * 
	 * @param zipName
	 *            压缩文件名
	 * @param File
	 *            [] 压缩的文件
	 * @return str
	 */
	public  String zipOutputFile(String zipName, List<String> filenames  ) {
		File zipFile = new File(zipName);

		ArrayList<File> filesToAdd = new ArrayList<File>();

		for(String filename:filenames)
			filesToAdd.add(new File(filename));


		InputStream input = null;
		ZipOutputStream zipOut = null;
		try {
			zipOut = new ZipOutputStream(new FileOutputStream(zipFile));
			for(File file:filesToAdd){
				if (!file.exists()) {
					logger.info("檔案 {}不存在！",file.getName());
					continue;
				}
				input = new FileInputStream(file);
				zipOut.putNextEntry(new ZipEntry(file.getName()));
				int temp = 0;
				while ((temp = input.read()) != -1) {
					zipOut.write(temp);
				}
				input.close();
			}
			zipOut.close();
		} catch (FileNotFoundException e) {
			logger.error("檔案 不存在{}",e);
		} catch (IOException e) {
			logger.error("產檔發生問題 {}",e);
		}catch (Exception e) {
			logger.error("產檔發生問題 {}",e);
		}finally {
			if (zipOut != null) {
				try {
					zipOut.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

			if (input != null) {
				try {
					input.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return zipName;
	}
}